﻿Public Class EmpList

    Dim se As String
    Dim dt As New DataTable
    Private Sub EmpList_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'With DataGridView1
        '    .EnableHeadersVisualStyles = False
        '    .Font = New Font(DataGridView1.Font, FontStyle.Bold)
        'End With

        'Dim columnHeaderStyle As New DataGridViewCellStyle
        'columnHeaderStyle.BackColor = Color.LightSkyBlue
        ' columnHeaderStyle.ForeColor = Color.Black
        'columnHeaderStyle.Font = _
        '    New Font("Khmer OS Bokor", 10, FontStyle.Bold)
        'DataGridView1.ColumnHeadersDefaultCellStyle = columnHeaderStyle
        UI.FormatDataGrid(DataGridView1)

        refreshdatarole()
        loadany()

        If MainMenu.per = 1 Then
            btnDel.Visible = True
        Else
            btnDel.Visible = False
        End If
        loadtotal()
        rname.Checked = True
    End Sub
    Private Sub loadany()
        dt = cmdp.ExecuteDataTable("SELECT EmpId,SexId,MinistryId FROM Employee")
        Dim a, b, c, d, e, f, g, h, i, k, l, m, n, o, p, q, r, s As Integer
        For x = 0 To dt.Rows.Count - 1
            If dt.Rows(x).Item(2) = 1 Then
                a += 1
                If dt.Rows(x).Item(1) = 2 Then
                    k += 1
                End If
            ElseIf dt.Rows(x).Item(2) = 2 Then
                b += 1
                If dt.Rows(x).Item(1) = 2 Then
                    l += 1
                End If
            ElseIf dt.Rows(x).Item(2) = 3 Then
                c += 1
                If dt.Rows(x).Item(1) = 2 Then
                    m += 1
                End If
            ElseIf dt.Rows(x).Item(2) = 4 Then
                d += 1
                If dt.Rows(x).Item(1) = 2 Then
                    m += 1
                End If
            ElseIf dt.Rows(x).Item(2) = 5 Then
                e += 1
                If dt.Rows(x).Item(1) = 2 Then
                    o += 1
                End If
            ElseIf dt.Rows(x).Item(2) = 6 Then
                f += 1
                If dt.Rows(x).Item(1) = 2 Then
                    p += 1
                End If
            ElseIf dt.Rows(x).Item(2) = 7 Then
                g += 1
                If dt.Rows(x).Item(1) = 2 Then
                    q += 1
                End If
            ElseIf dt.Rows(x).Item(2) = 8 Then
                h += 1
                If dt.Rows(x).Item(1) = 2 Then
                    r += 1
                End If
            ElseIf dt.Rows(x).Item(2) = 9 Then
                i += 1
                If dt.Rows(x).Item(1) = 2 Then
                    s += 1
                End If
            End If

            lbl1.Text = a : lbl11.Text = k
            lbl2.Text = b : lbl22.Text = l
            lbl3.Text = c : lbl33.Text = m
            lbl4.Text = d : lbl44.Text = n
            lbl5.Text = e : lbl55.Text = o
            lbl6.Text = f : lbl66.Text = p
            lbl7.Text = g : lbl77.Text = q
            lbl8.Text = h : lbl88.Text = r
            lbl9.Text = i : lbl99.Text = s
        Next
    End Sub
    Private Sub loadtotal()
        Dim dt2 As New DataTable
        dt2 = cmdp.ExecuteDataTable("SELECT SexId FROM Employee")
        lblnum.Text = dt2.Rows.Count
        Dim g As Integer = 0
        For i = 0 To dt2.Rows.Count - 1
            If dt2.Rows(i).Item(0) = 2 Then
                g += 1
            End If
        Next
        lblgirl.Text = g
    End Sub
    Private Sub refreshdata()
        cmdp.Parameters("@search") = txtsearch.Text & "%"
        Dim dt As New DataTable
        dt = cmdp.ExecuteDataTable("SELECT EmpId,EmpNo,EmpName,s.SexName,m.MinistryName,p.PositionName,r.RoleName FROM [Employee] e,[Sex] s,[Ministry] m,[Position] p,[Role] r WHERE e.SexId=s.SexId and e.MinistryId=m.MinistryId and e.PositionId=p.PositionId and e.RoleId=r.RoleId and EmpName like @search AND e.Active=true AND e.WorkRetire=true ORDER BY empid DESC")
        DataGridView1.DataSource = dt
    End Sub
    Private Sub refreshdatano()
        cmdp.Parameters("@search") = txtsearch.Text & "%"
        Dim dt As New DataTable
        dt = cmdp.ExecuteDataTable("SELECT EmpId,EmpNo,EmpName,s.SexName,m.MinistryName,p.PositionName,r.RoleName FROM [Employee] e,[Sex] s,[Ministry] m,[Position] p,[Role] r WHERE e.SexId=s.SexId and e.MinistryId=m.MinistryId and e.PositionId=p.PositionId and e.RoleId=r.RoleId and EmpNo like @search AND e.Active=true AND e.WorkRetire=true ")
        DataGridView1.DataSource = dt
    End Sub
    Private Sub refreshdatapos()
        cmdp.Parameters("@search") = txtsearch.Text & "%"
        Dim dt As New DataTable
        dt = cmdp.ExecuteDataTable("SELECT EmpId,EmpNo,EmpName,s.SexName,m.MinistryName,p.PositionName,r.RoleName FROM [Employee] e,[Sex] s,[Ministry] m,[Position] p,[Role] r WHERE e.SexId=s.SexId and e.MinistryId=m.MinistryId and e.PositionId=p.PositionId and e.RoleId=r.RoleId and p.PositionName like @search AND e.Active=true AND e.WorkRetire=true ORDER BY p.PositionId")
        DataGridView1.DataSource = dt
    End Sub
    Private Sub refreshdatarole()
        cmdp.Parameters("@search") = txtsearch.Text & "%"
        'DataGridView1.DataSource = cmdp.ExecuteDataTable("SELECT e.EmpNo,e.EmpName,s.SexName,m.MinistryName,p.PositionName,r.RoleName FROM Employee e INNER JOIN Sex s ON s.SexId=e.SexId INNER JOIN Ministry m ON m.MinistryId=e.MinistryId INNER JOIN [Position] p ON p.PositionId=e.PositionId INNER JOIN [Role] r ON r.RoleId=e.RoleId ")
        Dim dt As New DataTable
        dt = cmdp.ExecuteDataTable("SELECT EmpId,EmpNo,EmpName,s.SexName,m.MinistryName,p.PositionName,r.RoleName FROM [Employee] e,[Sex] s,[Ministry] m,[Position] p,[Role] r WHERE e.SexId=s.SexId and e.MinistryId=m.MinistryId and e.PositionId=p.PositionId and e.RoleId=r.RoleId and r.RoleName like @search AND e.Active=true AND e.WorkRetire=true ORDER BY r.RoleId")
        DataGridView1.DataSource = dt
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim frm = New Employee
        'frm.ShowDialog()
        If frm.ShowDialog() = Windows.Forms.DialogResult.OK Then
            refreshdata()
            loadany()
            loadtotal()
        End If

    End Sub

    Private Sub DataGridView1_DoubleClick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DataGridView1.DoubleClick
        Dim row As DataGridViewRow
        row = DataGridView1.SelectedRows(0)
        If IsDBNull(row.Cells(1).Value) Then
            Exit Sub
        End If
        Dim frm As New ViewEmp
        frm.id = row.Cells(1).Value
        'MessageBox.Show(frm.id)
        'frm.ShowDialog()
        If frm.ShowDialog() = Windows.Forms.DialogResult.OK Then
            refreshdatarole()
        End If
    End Sub

    Private Sub txtsearch_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtsearch.TextChanged
        If rname.Checked Then
            refreshdata()
        ElseIf rno.Checked Then
            refreshdatano()
        ElseIf rpos.Checked Then
            refreshdatapos()
        ElseIf rrole.Checked Then
            refreshdatarole()
        End If

    End Sub
    

    Private Sub btnDel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDel.Click
        Dim row As DataGridViewRow
        row = DataGridView1.SelectedRows(0)
        cmdp.Parameters("@id") = row.Cells(1).Value
        If MessageBox.Show("តើអ្នកពិតជាចង់លុបទិន្នន័យនេះមែនទេ?", "លុប", MessageBoxButtons.YesNo, MessageBoxIcon.Question) <> Windows.Forms.DialogResult.Yes Then
            Exit Sub
        End If
        cmdp.ExecuteNonQuery("DELETE FROM Employee WHERE EmpID=@id")
        MessageBox.Show("លុបបានសម្រេច")
        EmpList_Load(sender, e)
    End Sub

    
    Private Sub មលមនរតនToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles មលមនរតនToolStripMenuItem.Click
        DataGridView1_DoubleClick(sender, e)

    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

        DataGridView1.Rows(0).Selected = True

    End Sub
End Class